using System;
using System.Text;
using TccAg.VO;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TccAg.DA
{
    public class HorarioDA
    {
        private String queryInsert = 
            @"INSERT INTO horarios
                ( 
                 PERACAD, 
                 SEQUENCIA, 
                 HORAINI, 
                 HORAFIM, 
                 DIASEMANA, 
                 CURSO
                ) 
              VALUES
                ( 
                 @peracad, 
                 @sequencia, 
                 @horaini, 
                 @horafim, 
                 @diasemana, 
                 @curso
                )";
        private String queryUpdate = 
            @"UPDATE horarios SET 
                peracad = @peracad, 
                sequencia = @sequencia, 
                horaini = @horaini, 
                horafim = @horafim, 
                diasemana = @diasemana, 
                curso = @curso 
              WHERE 
                ID = @id";
        private String queryDelete = "DELETE FROM horarios WHERE ID = @id";
        private String queryLoadAll = 
            @"SELECT  
                id, 
                peracad, 
                sequencia, 
                horaini, 
                horafim, 
                diasemana, 
                curso 
              FROM 
                horarios";
        private String queryLoadByPk = 
            @"SELECT  
                id, 
                peracad, 
                sequencia, 
                horaini, 
                horafim, 
                diasemana, 
                curso 
              FROM 
                horarios 
              WHERE 
                ID = @id";
        private String queryLoadByFk = 
            @"SELECT  
                id, 
                peracad, 
                sequencia, 
                horaini, 
                horafim, 
                diasemana, 
                curso 
              FROM 
                horarios 
              WHERE 
                PERACAD = @peracad";
        private String queryLoadByProfessor =
            @"SELECT  
                id, 
                peracad, 
                sequencia, 
                horaini, 
                horafim, 
                diasemana, 
                curso 
              FROM 
                horarios 
              JOIN
                disponibilidade on (
                                    disponibilidade.horario = horarios.id
                                    AND
                                    disponibilidade.professor = @id
                                   )
              WHERE 
                PERACAD = @peracad
                AND
                ";
        private SqlConnection conexao = new SqlConnection("Pegar conexão");

        //INSERIR 
        public int inserir(HorarioVO horario)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryInsert;

                if (horario.Peracad.Id != 0)
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) horario.Peracad));
                else
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) DBNull.Value));

                if (horario.Sequencia != 0)
                        cmd.Parameters.Add(new SqlParameter("@sequencia", (object) horario.Sequencia));
                else
                        cmd.Parameters.Add(new SqlParameter("@sequencia", (object) DBNull.Value));

                if (horario.Horaini != null)
                        cmd.Parameters.Add(new SqlParameter("@horaini", (object) horario.Horaini));
                else
                        cmd.Parameters.Add(new SqlParameter("@horaini", (object) DBNull.Value));

                if (horario.Horafim != null)
                        cmd.Parameters.Add(new SqlParameter("@horafim", (object) horario.Horafim));
                else
                        cmd.Parameters.Add(new SqlParameter("@horafim", (object) DBNull.Value));

                if (horario.Diasemana != null)
                        cmd.Parameters.Add(new SqlParameter("@diasemana", (object) horario.Diasemana));
                else
                        cmd.Parameters.Add(new SqlParameter("@diasemana", (object) DBNull.Value));

                if (horario.Curso != 0)
                        cmd.Parameters.Add(new SqlParameter("@curso", (object) horario.Curso));
                else
                        cmd.Parameters.Add(new SqlParameter("@curso", (object) DBNull.Value));

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //ATUALIZAR 
        public int atualizar(HorarioVO horario)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                if (horario.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)horario.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryUpdate;

                if (horario.Peracad.Id != 0)
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) horario.Peracad));
                else
                        cmd.Parameters.Add(new SqlParameter("@peracad", (object) DBNull.Value));
                if (horario.Sequencia != 0)
                        cmd.Parameters.Add(new SqlParameter("@sequencia", (object) horario.Sequencia));
                else
                        cmd.Parameters.Add(new SqlParameter("@sequencia", (object) DBNull.Value));
                if (horario.Horaini != null)
                        cmd.Parameters.Add(new SqlParameter("@horaini", (object) horario.Horaini));
                else
                        cmd.Parameters.Add(new SqlParameter("@horaini", (object) DBNull.Value));
                if (horario.Horafim != null)
                        cmd.Parameters.Add(new SqlParameter("@horafim", (object) horario.Horafim));
                else
                        cmd.Parameters.Add(new SqlParameter("@horafim", (object) DBNull.Value));
                if (horario.Diasemana != 0)
                        cmd.Parameters.Add(new SqlParameter("@diasemana", (object) horario.Diasemana));
                else
                        cmd.Parameters.Add(new SqlParameter("@diasemana", (object) DBNull.Value));
                if (horario.Curso != 0)
                        cmd.Parameters.Add(new SqlParameter("@curso", (object) horario.Curso));
                else
                        cmd.Parameters.Add(new SqlParameter("@curso", (object) DBNull.Value));
                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //EXCLUIR 
        public int excluir(HorarioVO horario)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryDelete;

                if (horario.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)horario.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD ALL 
        public List<HorarioVO> loadAll()
        {
            try
            {
                List<HorarioVO> objRetorno = new List<HorarioVO>();
                HorarioVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        objTemp = new HorarioVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                           objTemp.Peracad.Id = (int)dr["peracad"];

                        if (dr["sequencia"] != DBNull.Value)
                           objTemp.Sequencia = (int)dr["sequencia"];

                        if (dr["horaini"] != DBNull.Value)
                           objTemp.Horaini = (String)dr["horaini"];

                        if (dr["horafim"] != DBNull.Value)
                           objTemp.Horafim = (String)dr["horafim"];

                        if (dr["diasemana"] != DBNull.Value)
                           objTemp.Diasemana = (Classes.Utils.Util.DiaSemana)dr["diasemana"];

                        if (dr["curso"] != DBNull.Value)
                           objTemp.Curso = (int)dr["curso"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY PK - Passa um objeto do tipo VO para em casos onde usar chave primária composta ter essas propriedades preenchidas
        public HorarioVO loadByPk(HorarioVO horario)
        {
            try
            {
                HorarioVO objRetorno = null;
                HorarioVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (horario.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)horario.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByPk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new HorarioVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                        {
                            PeriodoacademicoVO param = new PeriodoacademicoVO();
                            param.Id = (int)dr["peracad"];
                            objTemp.Peracad = new PeriodoacademicoDA().loadByPk(param);
                        }

                        if (dr["sequencia"] != DBNull.Value)
                           objTemp.Sequencia = (int)dr["sequencia"];

                        if (dr["horaini"] != DBNull.Value)
                           objTemp.Horaini = (String)dr["horaini"];

                        if (dr["horafim"] != DBNull.Value)
                           objTemp.Horafim = (String)dr["horafim"];

                        if (dr["diasemana"] != DBNull.Value)
                           objTemp.Diasemana = (Classes.Utils.Util.DiaSemana)dr["diasemana"];

                        if (dr["curso"] != DBNull.Value)
                           objTemp.Curso = (int)dr["curso"];

                        objRetorno = objTemp;
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY FK - Passa um objeto do tipo VO para em casos onde usar chave estrangeira composta ter essas propriedades preenchidas
        public List<HorarioVO> loadByFk(HorarioVO horario)
        {
            try
            {
                List<HorarioVO> objRetorno = new List<HorarioVO>();
                HorarioVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (horario.Peracad.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@peracad", (object)horario.Peracad));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByFk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new HorarioVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                        {
                            PeriodoacademicoVO param = new PeriodoacademicoVO();
                            param.Id = (int)dr["peracad"];
                            objTemp.Peracad = new PeriodoacademicoDA().loadByPk(param);
                        }

                        if (dr["sequencia"] != DBNull.Value)
                           objTemp.Sequencia = (int)dr["sequencia"];

                        if (dr["horaini"] != DBNull.Value)
                           objTemp.Horaini = (String)dr["horaini"];

                        if (dr["horafim"] != DBNull.Value)
                           objTemp.Horafim = (String)dr["horafim"];

                        if (dr["diasemana"] != DBNull.Value)
                           objTemp.Diasemana = (Classes.Utils.Util.DiaSemana)dr["diasemana"];

                        if (dr["curso"] != DBNull.Value)
                           objTemp.Curso = (int)dr["curso"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //DATATABLE LOAD ALL 
        public DataTable dataTableLoadAll()
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                SqlDataAdapter dtAdpt = new SqlDataAdapter(cmd);
                DataTable dtTbl = new DataTable("tab_horarios");

                dtAdpt.Fill(dtTbl);
                return dtTbl;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        public List<HorarioVO> loadByProfessor(HorarioVO horario, ProfessorVO professor)
        {
            try
            {
                List<HorarioVO> objRetorno = new List<HorarioVO>();
                HorarioVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (horario.Peracad.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@peracad", (object)horario.Peracad));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");
                if (professor.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)professor.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByProfessor;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new HorarioVO();
                        if (dr["id"] != DBNull.Value)
                            objTemp.Id = (int)dr["id"];

                        if (dr["peracad"] != DBNull.Value)
                        {
                            PeriodoacademicoVO param = new PeriodoacademicoVO();
                            param.Id = (int)dr["peracad"];
                            objTemp.Peracad = new PeriodoacademicoDA().loadByPk(param);
                        }

                        if (dr["sequencia"] != DBNull.Value)
                            objTemp.Sequencia = (int)dr["sequencia"];

                        if (dr["horaini"] != DBNull.Value)
                            objTemp.Horaini = (String)dr["horaini"];

                        if (dr["horafim"] != DBNull.Value)
                            objTemp.Horafim = (String)dr["horafim"];

                        if (dr["diasemana"] != DBNull.Value)
                            objTemp.Diasemana = (Classes.Utils.Util.DiaSemana)dr["diasemana"];

                        if (dr["curso"] != DBNull.Value)
                            objTemp.Curso = (int)dr["curso"];

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

    }
}
